﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using S.ReportForms.Domain.Dto;
using S.ReportForms.Infrastructure.Interface;
using System.Text;

namespace S.ReportForms.Infrastructure.Impl
{
    /// <summary>
    /// 种母配怀报表
    /// </summary>
    public class BreadMatchingRepository : IBreadMatchingRepository
    {
        private readonly IConfiguration configuration;

        public BreadMatchingRepository(IConfiguration configuration)
        {
            this.configuration = configuration;
        }
        #region 预估分娩率

        /// <summary>
        /// 预估分娩率
        /// </summary>
        /// <returns></returns>
        public async Task<List<NumberOfMatings>> GetBreadMatching(List<DateTime> dateTimeList)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            DateTime endDate= DateTime.Now;
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append(@$"SELECT 
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[2]}' AND '{dateTimeList[3]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingOne,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[4]}' AND '{dateTimeList[5]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingTwo,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[6]}' AND '{dateTimeList[7]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingThree,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[8]}' AND '{dateTimeList[9]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingFour,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[10]}' AND '{dateTimeList[11]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingFive,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[12]}' AND '{dateTimeList[13]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingSix,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[14]}' AND '{dateTimeList[15]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingSeven,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[16]}' AND '{dateTimeList[17]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingEight,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[18]}' AND '{dateTimeList[19]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingNine,
    SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[20]}' AND '{dateTimeList[21]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingTen,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[22]}' AND '{dateTimeList[23]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingEleven,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[24]}' AND '{dateTimeList[25]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingTwelve,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[26]}' AND '{dateTimeList[27]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingThirteen,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[28]}' AND '{dateTimeList[29]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingFourteen,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[30]}' AND '{dateTimeList[31]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingFifteen,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[32]}' AND '{dateTimeList[33]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingSixteen,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[34]}' AND '{dateTimeList[35]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingSeventeen,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[36]}' AND '{dateTimeList[37]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingEighteen,
	SUM(CASE WHEN (PregnancyDate BETWEEN '{dateTimeList[38]}' AND '{dateTimeList[39]}' AND PregnancytestResult = 4 OR DeliveryDate IS NOT NULL) THEN 1 ELSE 0 END) AS BreedingNineteen
FROM BreedingHistoryModel
WHERE BreedingDate BETWEEN '{dateTimeList[0]}' AND '{dateTimeList[1]}';");

                    List<NumberOfMatings> result = connection.Query<NumberOfMatings>(sqlQuery.ToString()).ToList();
                   
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// /获取分娩数
        /// </summary>
        /// <param name="dateTimeList"></param>
        /// <returns></returns>
        public async Task<List<int>> GetDeliveryDate(List<DateTime> dateTimeList)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT COUNT(*) FROM BreedingHistoryModel
                    WHERE BreedingDate BETWEEN '{dateTimeList[0]}' AND '{dateTimeList[1]}' 
                    AND DeliveryDate IS NOT NULL");

                    var result = connection.Query<int>(sqlQuery.ToString()).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }

        }
        /// <summary>
        /// 获取配种数
        /// </summary>
        /// <param name="dateTimeList"></param>
        /// <returns></returns>
        public async Task<List<int>> GetBreedingDate(List<DateTime> dateTimeList)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT 
                    COUNT(*) AS 配种总数量 FROM BreedingHistoryModel
                    WHERE BreedingDate BETWEEN  '{dateTimeList[0]}' AND '{dateTimeList[1]}';");

                    var result = connection.Query<int>(sqlQuery.ToString()).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region 配种分娩率

        /// <summary>
        /// 种母分娩率
        /// </summary>
        /// <param name="selectYear">查询的年</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <returns></returns>
        public async Task<List<BreedingDeliveryRateDto>> GetBreedingDeliveryRate(int selectYear, DateTime startDate, DateTime endDate)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT 
                    DATEPART(YEAR, BreedingDate) AS DateTimeYear,
                    DATEPART(MONTH, BreedingDate) AS DateTimeMonth,
                    COUNT(*) AS BreedingCount,
                    COUNT(CASE WHEN DeliveryDate IS NOT NULL THEN 1 END) AS DeliveryCount,
	                ROUND(CAST(COUNT(CASE WHEN DeliveryDate IS NOT NULL THEN 1 END) AS FLOAT) / COUNT(*) * 100, 2) AS BreedingDeliveryRate
                    FROM 
                    BreedingHistoryModel
                    WHERE 
                    DATEPART(YEAR, BreedingDate) = {selectYear}
                    AND BreedingDate BETWEEN '{startDate}' AND '{endDate}'
                    GROUP BY 
                    DATEPART(YEAR, BreedingDate),
                    DATEPART(MONTH, BreedingDate)
                    ORDER BY 
                    DATEPART(YEAR, BreedingDate),
                    DATEPART(MONTH, BreedingDate);");

                    var result = connection.Query<BreedingDeliveryRateDto>(sqlQuery.ToString()).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        #endregion


        #region 种母状态分布


        /// <summary>
        /// 繁殖状态
        /// </summary>
        /// <param name="varietyId">品种ID</param>
        /// <returns></returns>
        public async Task<List<ReproductiveStateCount>> GetReproductiveState(int varietyId)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT 
                            ReproductiveState, COUNT(*) AS TotalCount
                        FROM 
                            SheepFileModel
                        WHERE 
                            Sex = 2 AND PresenceState = 2 AND ReproductiveState <> 0");
                    if (varietyId > 0)
                    {
                        sqlQuery.Append(" AND Variety=@Variety");
                    }
                    sqlQuery.Append($@"
                    GROUP BY 
                        ReproductiveState

                    UNION

                    SELECT 
                        9, COUNT(*) AS TotalCount
                    FROM 
                        SheepFileModel
                    WHERE 
                        Sex = 2 AND PresenceState = 2 AND ReproductiveState NOT IN ( 7, 8)");
                    var result = connection.Query<ReproductiveStateCount>(sqlQuery.ToString(),new { Variety =varietyId}).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }

        }


        /// <summary>
        /// 妊娠前、中、后期、临产，超预产期
        /// </summary>
        /// <returns></returns>
        public async Task<List<PregnancyCount>> GetPregnancyCountDto()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT 
                          CASE
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 6 THEN '妊娠前期'
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 12 THEN '妊娠中期'
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 18 THEN '妊娠后期'
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 20 THEN '临产'
                            ELSE '超预产期'
                          END AS WeekRange,
                          COUNT(*) AS TotalCount
                        FROM 
                          BreedingHistoryModel
                        WHERE PregnancytestResult=4
                        GROUP BY 
                          CASE
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 6 THEN '妊娠前期'
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 12 THEN '妊娠中期'
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 18 THEN '妊娠后期'
                            WHEN DATEDIFF(WEEK, BreedingDate, GETDATE()) <= 20 THEN '临产'
                            ELSE '超预产期'
                          END;");

                    var result = connection.Query<PregnancyCount>(sqlQuery.ToString()).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }


        /// <summary>
        /// 妊检未检
        /// </summary>
        /// <returns></returns>
        public async Task<List<PregnancyNotCount>> GetPregnancyNotCountDto()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT 
                        SUM(CASE WHEN DATEDIFF(DAY, BreedingDate, GETDATE()) <= 35 THEN 1 ELSE 0 END) AS LessThan35Days,
                        SUM(CASE WHEN DATEDIFF(DAY, BreedingDate, GETDATE()) > 35 THEN 1 ELSE 0 END) AS GreaterThan35Days,
                        COUNT(*) AS TotalCount
                    FROM 
                        MaternalMating
                    WHERE 
                        BreedState != 2;");

                    var result = connection.Query<PregnancyNotCount>(sqlQuery.ToString()).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        
        
        #endregion
    }
}
